package com.ibm.risk.irmp.base.auth.dao;


import com.ibm.risk.irmp.base.auth.entity.SystemBizBranch;
import com.ibm.risk.irmp.base.auth.entity.SystemMngBranch;
import com.ibm.risk.irmp.base.auth.entity.SystemUser;
import com.ibm.risk.irmp.common.auth.vo.LoginUser;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SystemUserRepository extends JpaRepository<SystemUser, String>, JpaSpecificationExecutor<SystemUser> {

//    @Query(value = "SELECT USER_ID, USER_SERIAL_NUM, USER_NAME FROM RI_BASE_AUTH_USER WHERE USER_STATUS = 'T' " +
//            "AND USER_ID in ?1", nativeQuery = true)
//    List<Map<String, String>> findAllByUserIdIn(List<Long> userIdList, Pageable pageable);
//
//    @Query(value = "select mou.mngBranch FROM  SystemMngOrgUser mou WHERE mou.user.userId = ?1")
//    List<SystemMngBranch> getUserMngBranchList(Long userId);
//
//    @Modifying
//    @Query("delete from SystemMngOrgUser u where u.user = ?1")
//    void removeUserMngOrgs(SystemUser user);
//
//    @Modifying
//    @Query("delete from SystemFinOrgUser u where u.user = ?1")
//    void removeUserFinOrgs(SystemUser user);

//    @Query(value = "select mou.mngBranch FROM SystemMngOrgUser mou WHERE mou.user.userId = :userId " +
//            " union " +
//            "select ou FROM SystemMngOrgUser mou join SystemMngBranch ou on mou.mngBranch.branchCodeLevel1 = ou.branchCode " +
//            " WHERE mou.user.userId = :userId")
//    List<SystemMngBranch> getAllUserMngBranchList(@Param("userId") Long userId);

//    @Query(value = "select mou.mngBranch FROM SystemMngOrgUser mou WHERE mou.user.userId = :userId " +
//            " union " +
//            "select ou FROM SystemMngOrgUser mou join SystemMngBranch ou on mou.mngBranch.branchCodeLevel1 = ou.branchCode " +
//            " WHERE mou.user.userId = :userId and ou.belongLevel <= 1")
//    List<SystemMngBranch> getAllUserTopMngBranchList(@Param("userId") Long userId);
//
//    @Query(value = "select mou.bizBranch FROM SystemFinOrgUser mou WHERE mou.user.userId = :userId " +
//            " union " +
//            "select ou FROM SystemFinOrgUser mou join SystemFinBranch ou on mou.bizBranch.branchCodeLevel1 = ou.branchCode " +
//            " WHERE mou.user.username = :username ")
//    List<SystemFinBranch> getAllUserFinBranchList(@Param("username") String username);

    //    @Query(value = "select distinct * from (SELECT p.BRANCH_CODE, p.BRANCH_NAME, p.BELONG_LEVEL FROM RI_BASE_AUTH_FIN_BRANCH p\n" +
//            "  INNER JOIN RI_BASE_AUTH_ORG_USER ou ON ou.BRANCH_CODE = p.BRANCH_CODE \n" +
//            "  WHERE ou.user_id = :userId and p.BELONG_LEVEL <= 1\n" +
//            "  UNION \n" +
//            "  SELECT p.BRANCH_CODE, p.BRANCH_NAME, p.BELONG_LEVEL FROM RI_BASE_AUTH_FIN_BRANCH p\n" +
//            "  INNER JOIN RI_BASE_AUTH_ORG_USER ou ON ou.BRANCH_CODE = p.L1_BRANCH_CODE \n" +
//            "  WHERE ou.user_id = :userId and p.BELONG_LEVEL <= 1 \n" +
//            ") as atab ORDER BY BELONG_LEVEL, BRANCH_CODE", nativeQuery = true)
//    @Query(value = "select mou.bizBranch FROM SystemFinOrgUser mou WHERE mou.user.userId = :userId " +
//            " union " +
//            "select ou FROM SystemFinOrgUser mou join SystemFinBranch ou on mou.bizBranch.branchCodeLevel1 = ou.branchCode " +
//            " WHERE mou.user.userId = :userId and ou.belongLevel <= 1")
//    List<SystemFinBranch> getAllUserTopFinBranchList(@Param("userId") Long userId);


//    @Query(value = "select mou.finOrgs FROM SystemUser mou WHERE mou.username = ?1")
//    List<SystemFinBranch> getUserFinBranchList(String username);

    @Query(value = "select mou from SystemUser mou join mou.bizOrgs as b WHERE LOWER(mou.displayName) like LOWER(?2) and b.branchCode = ?1")
    Page<SystemUser> findByBizBranch(String bizBranchCode, String username, Pageable page);

    @Query(value = "from SystemUser mou join mou.mngOrgs as b WHERE b.branchCode = ?1")
    Page<SystemUser> findByMngBranch(String mngBranchCode, Pageable page);

    @Query("  FROM SystemBizBranch t\n" +
            "  WHERE t.consFlag = ?1 and t.belongLevel = ?2 and (t.manualFlag != 'C' or t.manualFlag is null)\n" +
            "  ORDER BY t.belongLevel, t.branchName ")
    List<SystemBizBranch> findBranchByConsFlag(String consFlag, Integer belongLevel);

    @Query("  FROM SystemMngBranch t\n" +
            "  WHERE t.consFlag = ?1  and t.belongLevel = ?2" +
            "  ORDER BY t.belongLevel, t.branchName ")
    List<SystemMngBranch> findMngBranchByConsFlag(String consFlag, Integer belongLevel);

    Page<SystemUser> findByDisplayNameLikeIgnoreCaseAndStatusNot(String displayName, LoginUser.Status status, Pageable pageable);

    Page<SystemUser> findByStatusNot(LoginUser.Status status, Pageable pageable);
}
